﻿using FarPoint.Win.Spread;
using Spread.Common.Features.Sample;
using System;
using System.Windows.Forms;

namespace Spread.Calculation.Features
{
    public partial class GoalSeek : BaseSampleControl
    {
        public GoalSeek()
        {
            InitializeComponent();
            Name = "Goal Seek";
            _fpSpread.ActiveSheetChanged += OnActiveSheetChanged;
            SetupProfitGoalSeekSheet(_fpSpread);
            SetupLoanRepaymentGoalSeekSheet(_fpSpread);
            SetupGoalSeekUI();
        }

        private void SetupLoanRepaymentGoalSeekSheet(FpSpread spread)
        {
            var workSheet = spread.AsWorkbook().Worksheets.Add("Loan Repayment Goal Seek");

            // Styling
            spread.Sheets[1].Cells["A2:A5"].BackColor = System.Drawing.Color.SkyBlue;
            spread.Sheets[1].Cells["A2:B6"].Border = new FarPoint.Win.LineBorder(System.Drawing.Color.Black, 1);
            spread.Sheets[1].Cells["A6"].BackColor = System.Drawing.Color.Orange;
            workSheet.Columns["A"].ColumnWidth = 150;
            workSheet.Columns["B"].ColumnWidth = 250;
            workSheet.Cells["A2:A6"].Font.Bold = true;
            workSheet.Cells["B6"].Font.Bold = true;

            // Data
            workSheet.Cells["A2"].Value = "Amount";
            workSheet.Cells["A3"].Value = "Rate of Interest";
            workSheet.Cells["A4"].Value = "Number of Periods";
            workSheet.Cells["A5"].Value = "EMI";
            workSheet.Cells["A6"].Value = "Total Amount Repaid";
            workSheet.Cells["B2"].Value = 18000;
            workSheet.Cells["B3"].Value = 0.1;
            workSheet.Cells["B3"].NumberFormat = "0.00%";
            workSheet.Cells["B4"].Value = 45;
            workSheet.Cells["B5"].Formula = "PMT($B3/12,$B4,-$B2, 0, 0)";
            workSheet.Cells["B6"].Formula = "$B5*$B4";
            workSheet.Cells["B2"].NumberFormat = "$###,###.00";
            workSheet.Cells["B4:B6"].NumberFormat = "$###,###.00";
        }

        private void SetupProfitGoalSeekSheet(FpSpread spread)
        {
            var workSheet = spread.Sheets[0].AsWorksheet();

            // Styling
            spread.Sheets[0].Cells["A2:A7"].BackColor = System.Drawing.Color.SkyBlue;
            spread.Sheets[0].Cells["A2:C8"].Border = new FarPoint.Win.LineBorder(System.Drawing.Color.Black, 1);
            spread.Sheets[0].Cells["A8"].BackColor = System.Drawing.Color.Orange;
            workSheet.Name = "Profit Goal Seek";
            workSheet.Columns["A"].ColumnWidth = 150;
            workSheet.Columns["B"].ColumnWidth = 250;
            workSheet.Columns["C"].ColumnWidth = 200;
            workSheet.Cells["A2:A8"].Font.Bold = true;
            workSheet.Cells["C8"].Font.Bold = true;

            // Data
            workSheet.Cells["A2"].Value = "Price";
            workSheet.Cells["A3"].Value = "Quantity";
            workSheet.Cells["A4"].Value = "Variable Cost";
            workSheet.Cells["A5"].Value = "Fixed Cost";
            workSheet.Cells["A6"].Value = "Total Revenue";
            workSheet.Cells["A7"].Value = "Total Cost";
            workSheet.Cells["A8"].Value = "Profit";
            workSheet.Cells["B4"].Value = "(Price * 10%)";
            workSheet.Cells["B6"].Value = "(Price * Quantity)";
            workSheet.Cells["B7"].Value = "Fixed Cost + (Variable Cost * Quantity)";
            workSheet.Cells["B8"].Value = "Total Revenue - Total Cost";
            workSheet.Cells["C2"].Value = 18000;
            workSheet.Cells["C3"].Value = 100;
            workSheet.Cells["C4"].Formula = "ROUND($C2*10%, 0)";
            workSheet.Cells["C5"].Value = 3000000;
            workSheet.Cells["C6"].Formula = "ROUND($C2*$C3, 2)";
            workSheet.Cells["C7"].Formula = "ROUND($C5+($C4*$C3), 2)";
            workSheet.Cells["C8"].Formula = "ROUND($C6-$C7, 2)";
            workSheet.Cells["C2"].NumberFormat = "$###,###.00";
            workSheet.Cells["C4:C8"].NumberFormat = "$###,###.00";
        }

        private void OnGoalSeekClick(object sender, EventArgs e)
        {
            var worksheet = _fpSpread.ActiveSheet.AsWorksheet();
            if (!double.TryParse(_txtToValue.Text, out var goal))
                MessageBox.Show("Please provide a valid numeric value", "Invalid input", MessageBoxButtons.OK, MessageBoxIcon.Error);
            
            // Goal Seek
            worksheet.Cells[_txtSetCell.Text].GoalSeek(goal, worksheet.Cells[_txtChangingCell.Text]);
        }

        private void OnActiveSheetChanged(object sender, EventArgs e)
        {
            SetupGoalSeekUI();
        }

        private void SetupGoalSeekUI()
        {
            if (_fpSpread.ActiveSheetIndex == 0)
            {
                _txtSetCell.Text = "$C8";
                _txtToValue.Text = "50000";
                _txtChangingCell.Text = "$C3";
                _lblValue.Text = "To value: (Goal Profit)";
            }
            else
            {
                _txtSetCell.Text = "$B6";
                _txtToValue.Text = "25000";
                _txtChangingCell.Text = "$B4";
                _lblValue.Text = "To value: (Goal Repay Amount)";
            }

            _txtSetCell.Enabled = false;
            _txtChangingCell.Enabled = false;
        }

        private void OnGoalSeekDialogMenuItemClick(object sender, EventArgs e)
        {
            Form goalSeekDialog = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.GoalSeek(_fpSpread);
            goalSeekDialog.Show(this);
        }
    }
}

//
// GoalSeek.Designer.cs
//

namespace Spread.Calculation.Features
{
    partial class GoalSeek
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(GoalSeek));
            this.splitContainer1 = new System.Windows.Forms.SplitContainer();
            this.splitContainer2 = new System.Windows.Forms.SplitContainer();
            this.nameBox1 = new FarPoint.Win.Spread.NameBox();
            this._fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, ((object)(resources.GetObject("splitContainer2.Panel1.Controls"))));
            this._fpSpread_Sheet1 = this._fpSpread.GetSheet(0);
            this.panel1 = new System.Windows.Forms.Panel();
            this.formulaTextBox1 = new FarPoint.Win.Spread.FormulaTextBox();
            this.label1 = new System.Windows.Forms.Label();
            this._lblValue = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this._txtSetCell = new System.Windows.Forms.TextBox();
            this.button1 = new System.Windows.Forms.Button();
            this._txtToValue = new System.Windows.Forms.TextBox();
            this._txtChangingCell = new System.Windows.Forms.TextBox();
            this.menuStrip1 = new System.Windows.Forms.MenuStrip();
            this.goalSeekDialogToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.splitContainer1)).BeginInit();
            this.splitContainer1.Panel1.SuspendLayout();
            this.splitContainer1.Panel2.SuspendLayout();
            this.splitContainer1.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.splitContainer2)).BeginInit();
            this.splitContainer2.Panel1.SuspendLayout();
            this.splitContainer2.Panel2.SuspendLayout();
            this.splitContainer2.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).BeginInit();
            this.panel1.SuspendLayout();
            this.menuStrip1.SuspendLayout();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.splitContainer1);
            this.pnlMain.Controls.Add(this.menuStrip1);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this._txtChangingCell);
            this.pnlControls.Controls.Add(this._txtToValue);
            this.pnlControls.Controls.Add(this.button1);
            this.pnlControls.Controls.Add(this._txtSetCell);
            this.pnlControls.Controls.Add(this.label3);
            this.pnlControls.Controls.Add(this._lblValue);
            this.pnlControls.Controls.Add(this.label1);
            this.pnlControls.Size = new System.Drawing.Size(210, 599);
            // 
            // splitContainer1
            // 
            this.splitContainer1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.splitContainer1.IsSplitterFixed = true;
            this.splitContainer1.Location = new System.Drawing.Point(0, 28);
            this.splitContainer1.Name = "splitContainer1";
            this.splitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal;
            // 
            // splitContainer1.Panel1
            // 
            this.splitContainer1.Panel1.Controls.Add(this.splitContainer2);
            // 
            // splitContainer1.Panel2
            // 
            this.splitContainer1.Panel2.Controls.Add(this._fpSpread);
            this.splitContainer1.Size = new System.Drawing.Size(636, 571);
            this.splitContainer1.SplitterDistance = 25;
            this.splitContainer1.TabIndex = 0;
            // 
            // splitContainer2
            // 
            this.splitContainer2.Dock = System.Windows.Forms.DockStyle.Fill;
            this.splitContainer2.Location = new System.Drawing.Point(0, 0);
            this.splitContainer2.Name = "splitContainer2";
            // 
            // splitContainer2.Panel1
            // 
            this.splitContainer2.Panel1.Controls.Add(this.nameBox1);
            // 
            // splitContainer2.Panel2
            // 
            this.splitContainer2.Panel2.Controls.Add(this.panel1);
            this.splitContainer2.Size = new System.Drawing.Size(636, 25);
            this.splitContainer2.SplitterDistance = 105;
            this.splitContainer2.TabIndex = 0;
            // 
            // nameBox1
            // 
            this.nameBox1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.nameBox1.Location = new System.Drawing.Point(0, 0);
            this.nameBox1.MaxLength = 255;
            this.nameBox1.Name = "nameBox1";
            this.nameBox1.Size = new System.Drawing.Size(105, 24);
            this.nameBox1.TabIndex = 0;
            // Attach nameBox1 to _fpSpread
            this.nameBox1.Attach(this._fpSpread);
            // 
            // _fpSpread
            // 
            this._fpSpread.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0";
            this._fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this._fpSpread.Font = new System.Drawing.Font("Calibri", 11F);
            this._fpSpread.Location = new System.Drawing.Point(0, 0);
            this._fpSpread.Name = "_fpSpread";
            this._fpSpread.Size = new System.Drawing.Size(636, 542);
            this._fpSpread.TabIndex = 0;
            // 
            // panel1
            // 
            this.panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.panel1.Controls.Add(this.formulaTextBox1);
            this.panel1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.panel1.Location = new System.Drawing.Point(0, 0);
            this.panel1.Name = "panel1";
            this.panel1.Size = new System.Drawing.Size(527, 25);
            this.panel1.TabIndex = 0;
            // 
            // formulaTextBox1
            // 
            this.formulaTextBox1.AcceptsTab = true;
            this.formulaTextBox1.BorderStyle = System.Windows.Forms.BorderStyle.None;
            this.formulaTextBox1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.formulaTextBox1.Location = new System.Drawing.Point(0, 0);
            this.formulaTextBox1.Margin = new System.Windows.Forms.Padding(0);
            this.formulaTextBox1.Name = "formulaTextBox1";
            this.formulaTextBox1.RightToLeft = System.Windows.Forms.RightToLeft.No;
            this.formulaTextBox1.Size = new System.Drawing.Size(525, 23);
            this.formulaTextBox1.TabIndex = 0;
            // Attach formulaTextBox1 to _fpSpread
            this.formulaTextBox1.Attach(this._fpSpread);
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(7, 29);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(54, 16);
            this.label1.TabIndex = 0;
            this.label1.Text = "Set cell:";
            // 
            // _lblValue
            // 
            this._lblValue.AutoSize = true;
            this._lblValue.Location = new System.Drawing.Point(7, 93);
            this._lblValue.Name = "_lblValue";
            this._lblValue.Size = new System.Drawing.Size(63, 16);
            this._lblValue.TabIndex = 1;
            this._lblValue.Text = "To value:";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Location = new System.Drawing.Point(7, 156);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(108, 16);
            this.label3.TabIndex = 2;
            this.label3.Text = "By changing cell:";
            // 
            // _txtSetCell
            // 
            this._txtSetCell.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._txtSetCell.Location = new System.Drawing.Point(10, 59);
            this._txtSetCell.Name = "_txtSetCell";
            this._txtSetCell.Size = new System.Drawing.Size(182, 22);
            this._txtSetCell.TabIndex = 3;
            // 
            // button1
            // 
            this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.button1.Location = new System.Drawing.Point(10, 225);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(182, 30);
            this.button1.TabIndex = 6;
            this.button1.Text = "Goal Seek";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.OnGoalSeekClick);
            // 
            // _txtToValue
            // 
            this._txtToValue.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._txtToValue.Location = new System.Drawing.Point(10, 122);
            this._txtToValue.Name = "_txtToValue";
            this._txtToValue.Size = new System.Drawing.Size(182, 22);
            this._txtToValue.TabIndex = 7;
            // 
            // _txtChangingCell
            // 
            this._txtChangingCell.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this._txtChangingCell.Location = new System.Drawing.Point(10, 185);
            this._txtChangingCell.Name = "_txtChangingCell";
            this._txtChangingCell.Size = new System.Drawing.Size(182, 22);
            this._txtChangingCell.TabIndex = 8;
            // 
            // menuStrip1
            // 
            this.menuStrip1.ImageScalingSize = new System.Drawing.Size(20, 20);
            this.menuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.goalSeekDialogToolStripMenuItem});
            this.menuStrip1.Location = new System.Drawing.Point(0, 0);
            this.menuStrip1.Name = "menuStrip1";
            this.menuStrip1.Size = new System.Drawing.Size(636, 28);
            this.menuStrip1.TabIndex = 1;
            this.menuStrip1.Text = "menuStrip1";
            // 
            // goalSeekDialogToolStripMenuItem
            // 
            this.goalSeekDialogToolStripMenuItem.Name = "goalSeekDialogToolStripMenuItem";
            this.goalSeekDialogToolStripMenuItem.Size = new System.Drawing.Size(138, 24);
            this.goalSeekDialogToolStripMenuItem.Text = "Goal Seek Dialog";
            this.goalSeekDialogToolStripMenuItem.Click += new System.EventHandler(this.OnGoalSeekDialogMenuItemClick);
            // 
            // GoalSeek
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "GoalSeek";
            this.pnlMain.ResumeLayout(false);
            this.pnlMain.PerformLayout();
            this.pnlControls.ResumeLayout(false);
            this.pnlControls.PerformLayout();
            this.splitContainer1.Panel1.ResumeLayout(false);
            this.splitContainer1.Panel2.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.splitContainer1)).EndInit();
            this.splitContainer1.ResumeLayout(false);
            this.splitContainer2.Panel1.ResumeLayout(false);
            this.splitContainer2.Panel2.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.splitContainer2)).EndInit();
            this.splitContainer2.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this._fpSpread)).EndInit();
            this.panel1.ResumeLayout(false);
            this.menuStrip1.ResumeLayout(false);
            this.menuStrip1.PerformLayout();
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.SplitContainer splitContainer1;
        private System.Windows.Forms.SplitContainer splitContainer2;
        private FarPoint.Win.Spread.NameBox nameBox1;
        private System.Windows.Forms.Panel panel1;
        private FarPoint.Win.Spread.FormulaTextBox formulaTextBox1;
        private FarPoint.Win.Spread.FpSpread _fpSpread;
        private FarPoint.Win.Spread.SheetView _fpSpread_Sheet1;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label _lblValue;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.TextBox _txtSetCell;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.TextBox _txtToValue;
        private System.Windows.Forms.TextBox _txtChangingCell;
        private System.Windows.Forms.MenuStrip menuStrip1;
        private System.Windows.Forms.ToolStripMenuItem goalSeekDialogToolStripMenuItem;
    }
}
